﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ModelLayer;

namespace FremtidensPlejehjem_Server.Database
{
    public static class DbRestaurant
    {
        #region Restaurant
        /// <summary>
        /// Creates a restaurant, based on the given parameters.
        /// </summary>
        /// <param name="restaurantName">Specifies the name of the restaurant.</param>
        /// <param name="roomNumber">Specifies which room the restaurant is located in.</param>
        public static void CreateRestaurant(string restaurantName, int roomNumber)
        {
            string query = "insert into Restaurant (restaurantName) values (@restaurantName) " +
                           "insert into Restaurant_Room (restaurantID, roomNumber) values (@restaurantID, @roomNumber)";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@restaurantName", restaurantName);
                    cmd.Parameters.AddWithValue("@restaurantID", DbGeneral.GetNextId("restaurantID", "Restaurant"));
                    cmd.Parameters.AddWithValue("@roomNumber", roomNumber);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Retrieves a restaurant based on the restaurantId parameter.
        /// </summary>
        /// <param name="restaurantId">Specifies which restaurant to retrieve.</param>
        /// <returns>Returns a Restaurant object.</returns>
        public static Restaurant GetRestaurant(int restaurantId)
        {
            string query = "select res.restaurantID, res.restaurantName, room.roomNumber room.purpose " +
                           "from Restaurant res, Room room, Restaurant_Room rr" +
                           "where res.restaurantID = @restaurantId " +
                           "and rr.restaurantID res.restaurantID" +
                           "and room.roomNumber = rr.roomNumber";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    Restaurant res = null;

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@restaurantID", restaurantId);

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        res = new Restaurant();

                        res.Name = dr["restuarantName"].ToString();
                        res.Room = new Room(Convert.ToInt32(dr["roomNumber"]), dr["purpose"].ToString());
                        res.Menues = GetMenuesByRestaurant(Convert.ToInt32(dr["restuarantID"]));
                    }

                    conn.Close();

                    return res;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Updates a restaurant based on the given parameters.
        /// </summary>
        /// <param name="restaurantName">Specifies the name of the restaurant.</param>
        /// <param name="restaurantId">Specifies the restaurant to be updated,</param>
        public static void UpdateRestaurant(string restaurantName, int restaurantId)
        {
            string query = "update Restaurant " +
                           "set restaurantName = @restaurantName " +
                           "where restaurantID = @restaurantId";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@restaurantName", restaurantName);
                    cmd.Parameters.AddWithValue("@restaurantId", restaurantId);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Deletes a restaurant.
        /// </summary>
        /// <param name="restaurantId">Specifies the restaurant to be deleted.</param>
        public static void DeleteRestaurant(int restaurantId)
        {
            string query = "Delete Restaurant " +
                           "where restaurantID = @restaurantId";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@restaurantId", restaurantId);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }
        #endregion

        #region Menu
        /// <summary>
        /// Creates a menu, based on the restaurantName parameter.
        /// </summary>
        /// <param name="menuName">Specifies the name of the menu.</param>
        public static void CreateMenu(string menuName)
        {
            string query = "insert into Menu (menuName) values (@menuName)";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@menuName", menuName);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Adds a menu to a restaurant, based on the given parameters.
        /// </summary>
        /// <param name="restaurantId">Specifies the restaurant to be used.</param>
        /// <param name="menuId">Specifies the menu to be used.</param>
        public static void AddMenuToRestaurant(int restaurantId, int menuId)
        {
            string query = "insert into Restaurant_Menu (restaurantID, menuID) values (@restaurantId, @menuId)";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@restaurantId", restaurantId);
                    cmd.Parameters.AddWithValue("@menuId", menuId);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Retrieves a list of menues.
        /// </summary>
        /// <param name="restaurantID">Specifies which restaurant to retrieve menues from.</param>
        /// <returns>Returns a list of Menu objects.</returns>
        public static List<Menu> GetMenuesByRestaurant(int restaurantID)
        {
            string query = "select menu.menuID, menu.menuName" +
                           "from Menu menu, Restaurant_Menu rm, Restaurant res " +
                           "where rm.restaurantID = @restaurantId " +
                           "and rm.menuID = menu.MenuID";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    List<Menu> menues = new List<Menu>();

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@restaurantId", restaurantID);

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Menu menu = new Menu();

                        menu.Name = dr["menuName"].ToString();
                        menu.MenuItems = GetMenuItemsByMenu(Convert.ToInt32(dr["menuID"]));

                        menues.Add(menu);
                    }

                    conn.Close();

                    return menues;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Updates a menu based on the given parameters.
        /// </summary>
        /// <param name="menuName">Specifies the name of the menu.</param>
        /// <param name="menuId">Specifies the menu to be updated.</param>
        public static void UpdateMenu(string menuName, int menuId)
        {
            string query = "update Menu " +
                           "set menuName = @menuName " +
                           "where menuID = @restaurantId";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@menuName", menuName);
                    cmd.Parameters.AddWithValue("@menuId", menuId);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Deletes a menu.
        /// </summary>
        /// <param name="menuId">Specifies the menu to be deleted.</param>
        public static void DeleteMenu(int menuId)
        {
            string query = "Delete Menu " +
                           "where menuID = @menuId";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@menuId", menuId);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        #endregion

        #region MenuItem

        /// <summary>
        /// Creates a menuItem, based on the given parameter.
        /// </summary>
        /// <param name="menuItemName">Specifies the name of the menuItem.</param>
        /// <param name="menuItemDescription">Specifies the description of the menuItem.</param>
        /// <param name="menuItemPrice">Specifies the price of the menuItem</param>
        /// <param name="menuItemIsDailyDeal">Specifies if the menuitem is a daily deal</param>
        public static void CreateMenuItem(string menuItemName, string menuItemDescription, double menuItemPrice, bool menuItemIsDailyDeal)
        {
            string query = "insert into MenuItem (menuItemName, menuItemDescription, menuItemPrice, menuItemIsDailyDeal) values (@menuItemName, @menuItemDescription, @menuItemPrice. @menuItemIsDailyDeal)";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@menuItemName", menuItemName);
                    cmd.Parameters.AddWithValue("@menuItemDescription", menuItemDescription);
                    cmd.Parameters.AddWithValue("@menuItemPrice", menuItemPrice);
                    cmd.Parameters.AddWithValue("@menuItemIsDailyDeal", menuItemIsDailyDeal);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Adds a menuItem to a menu, based on the given parameters.
        /// </summary>
        /// <param name="menuItemId">Specifies the menuItem to be used.</param>
        /// <param name="menuId">Specifies the menu to be used.</param>
        public static void AddMenuItemToMenu(int menuItemId, int menuId)
        {
            string query = "insert into Menu_MenuItem (menuItemID, menuID) values (@menuItemId, @menuId)";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@menuItemId", menuItemId);
                    cmd.Parameters.AddWithValue("@menuId", menuId);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Retrieves a list of menuItems based on the chosen menu.
        /// </summary>
        /// <param name="menuId">Specifies the menu to retrieve menuItems from.</param>
        /// <returns>Returns a list of MenuItem objects.</returns>
        public static List<MenuItem> GetMenuItemsByMenu(int menuId)
        {
            string query = "select mi.menuItemName, mi.menuItemDescription, mi.menuItemPrice, mi.menuItemIsDailyDeal" +
                           "from MenuItem mi, Menu menu Menu_MenuItem mmi " +
                           "where menu.menuID = @menuId " +
                           "and mmi.menuItemId = mi.menuItemId " +
                           "and menu.menuID = mmi.menuID";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    List<MenuItem> menuItems = new List<MenuItem>();

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@menuId", menuId);

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        MenuItem mi = new MenuItem();

                        mi.Name = dr["menuItemName"].ToString();
                        mi.Description = dr["menuItemDescription"].ToString();
                        mi.Price = Convert.ToDouble(dr["menuItemPrice"]);
                        mi.IsDailyDeal = Convert.ToBoolean(dr["menuItemIsDailyDeal"]);

                        menuItems.Add(mi);
                    }

                    conn.Close();

                    return menuItems;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Updates a menuItem based on the given parameters.
        /// </summary>
        /// <param name="menuItemName">Specifies the name of the menuItem.</param>
        /// <param name="menuItemDescription">Specifies the description of the menuItem.</param>
        /// <param name="menuItemPrice">Specifies the price of the menuItem</param>
        /// /// <param name="menuItemIsDailyDeal">Specifies if the menuitem is a daily deal</param>
        public static void UpdateMenuItem(string menuItemName, string menuItemDescription, double menuItemPrice, int menuItemId, bool menuItemIsDailyDeal)
        {
            string query = "update MenuItem " +
                           "set menuItemName = @menuItemName, menuItemDescription = @menuItemDescription, menuItemPrice = @menuItemPrice, menuItemIsDailyDeal = @menuItemIsDailyDeal " +
                           "where menuItemID = @menuItemId";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@menuItemName", menuItemName);
                    cmd.Parameters.AddWithValue("@menuItemDescription", menuItemDescription);
                    cmd.Parameters.AddWithValue("@menuItemPrice", menuItemPrice);
                    cmd.Parameters.AddWithValue("@menuItemId", menuItemId);
                    cmd.Parameters.AddWithValue("@menuItemIsDailyDeal", menuItemIsDailyDeal);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Deletes a menuItem.
        /// </summary>
        /// <param name="menuItemId">Specifies the menuItem to be deleted.</param>
        public static void DeleteMenuItem(int menuItemId)
        {
            string query = "Delete MenuItem " +
                           "where menuItemId = @menuItemId";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@menuItemId", menuItemId);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Retrieves a list of menuitems that is set as daily deals.
        /// </summary>
        /// <returns>Returns a list of MenuItem objects that is daily deals.</returns>
        public static List<MenuItem> GetDailyDeals()
        {
            string query = "select mi.menuItemName, mi.menuItemDescription, mi.menuItemPrice, mi.menuItemIsDailyDeal" +
                           "from MenuItem mi " +
                           "where mi.menuItemIsDailyDeal = 1";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    List<MenuItem> menuItems = new List<MenuItem>();

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        MenuItem mi = new MenuItem();

                        mi.Name = dr["menuItemName"].ToString();
                        mi.Description = dr["menuItemDescription"].ToString();
                        mi.Price = Convert.ToDouble(dr["menuItemPrice"]);
                        mi.IsDailyDeal = Convert.ToBoolean(dr["menuItemIsDailyDeal"]);

                        menuItems.Add(mi);
                    }

                    conn.Close();

                    return menuItems;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        #endregion
    }
}
